package com.project.dao.Impl;

import com.project.bean.CutPagBean;
import com.project.bean.RoomBean;
import com.project.dao.IRoomDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Repository
public class RoomDaoImpl extends BaseDao implements IRoomDao {
    @Override
    public void updateRoomStatus(Integer id, String roomStatUs) {
        this.setConnection();
        try {
            ps = con.prepareStatement("UPDATE r_room SET r_stat=? WHERE pk_roomId=?");
            ps.setObject(1, roomStatUs);
            ps.setObject(2, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public CutPagBean<RoomBean> findAllRoom(Integer pageNo, String info, String state, String address, String type) {
        CutPagBean<RoomBean> cutPageBean = new CutPagBean<>();
        this.setConnection();
        try {
            String sql = "SELECT r.*,COUNT(s.pk_stId) num FROM r_room r LEFT JOIN s_student s ON r.pk_roomId=s.fk_rooId WHERE 1=1 ";
            String dynaSql = "";
            String dynaSql2 = "";
            String dynaSql3 = "";

            if (address != null && !(address.equals(""))) {
                dynaSql += " AND r.r_address LIKE '%" + address + "%' ";
            }

            if (state != null && !(state.equals(""))&& !state.equals("不限")) {
                dynaSql += " AND r.r_stat ='" + state + "' ";
            }

            if (type != null && !(type.equals(""))&&! type.equals("不限")) {
                dynaSql += " AND r.r_type ='" + type + "' ";
            }

            if (info != null && !(info.equals(""))) {
                if (info.equals("不限")) {
                    dynaSql2="";
                }
                if (info.equals("可住房间")) {
                    dynaSql2 += " HAVING num<r.r_capacity ";
                    dynaSql3 += " and num<r.r_capacity ";
                }
            }
            ps = con.prepareStatement(sql + dynaSql + "GROUP BY r.`pk_roomId`" + dynaSql2 + "limit ?,?");
            ps.setObject(1, (pageNo - 1) * CutPagBean.PAGESIZE);
            ps.setObject(2, CutPagBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()) {
                cutPageBean.getList().add(this.getRoomBean());
            }

            ps = con.prepareStatement("SELECT COUNT(pk_roomId) num1 FROM((SELECT r.*,COUNT(s.pk_stId) num FROM r_room r LEFT JOIN s_student s ON r.pk_roomId=s.fk_rooId WHERE 1=1 " + dynaSql + dynaSql3 + "GROUP BY r.`pk_roomId` )AS a)");
            rs = ps.executeQuery();
            while (rs.next()) {
                cutPageBean.setCount(rs.getInt("num1"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
        return cutPageBean;
    }

    private RoomBean getRoomBean() {
        RoomBean roomBean = new RoomBean();
        try {
            roomBean.setId(rs.getInt("pk_roomId"));
            roomBean.setAddress(rs.getString("r_address"));
            roomBean.setType(rs.getString("r_type"));
            roomBean.setState(rs.getString("r_stat"));
            roomBean.setNumber(rs.getInt("r_number"));
            roomBean.setResident(rs.getInt("num"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ;
        return roomBean;
    }

    @Override
    public void delRoom(Integer roomId) {
        this.setConnection();
        try {
            ps = con.prepareStatement("DELETE FROM r_room WHERE pk_roomId=?");
            ps.setObject(1, roomId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public RoomBean findByRooId(int id) {
        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT *FROM r_room WHERE pk_roomId=?");
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(rs.getInt("pk_roomId"));
                roomBean.setAddress(rs.getString("r_address"));
                roomBean.setNumber(rs.getInt("r_number"));
                roomBean.setState(rs.getString("r_stat"));
                roomBean.setType(rs.getString("r_type"));
                return roomBean;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }

        return null;
    }

    @Override
    public void addRoom(RoomBean roomBean) {
        this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO r_room(r_address,r_stat,r_type,r_number)\n" +
                    "VALUES(?,?,?,?)");
            ps.setObject(1, roomBean.getAddress());
            ps.setObject(2, roomBean.getState());
            ps.setObject(3, roomBean.getType());
            ps.setObject(4, roomBean.getNumber());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public List<RoomBean> findByGender(String gender) {
        List<RoomBean> list = new ArrayList<>();
        this.setConnection();
        try {
            String str="SELECT rr.*,t.co FROM r_room rr LEFT JOIN (\n" +
                    "   SELECT ss.*, COUNT(ss.pk_stId) co FROM s_student ss GROUP BY ss.fk_rooId) t \n" +
                    " ON rr.pk_roomId = t.fk_rooId WHERE (rr.r_number > t.co OR t.co IS NULL) AND  rr.r_type LIKE";
            str += "'%" + gender + "%'";
            ps = con.prepareStatement(str);
            rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(rs.getInt("pk_roomId"));
                roomBean.setAddress(rs.getString("r_address"));
                list.add(roomBean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return list;
    }

}
